package biz.bbtec.sbm.store.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.store.bean.CategoryBean;
import biz.bbtec.sbm.store.bean.ItemBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class ItemService {

	dbConnection db = new dbConnection();

	public List showallitem(UserBean user) {
		List<ItemBean> list = new ArrayList<ItemBean>();

		String sql1 = "select * from item where orgid = ?";
		String sql2 = "select * from store where itemid = ?";
		String sql3 = "select name,parentid from category where catid = ? and orgid = ?";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			rs = past.executeQuery();
			while (rs.next()) {
				ItemBean item = new ItemBean();
				item.setDescription(rs.getString("description"));
				item.setItemid(rs.getInt("itemid"));

				PreparedStatement ps = conn.prepareStatement(sql2);
				ps.setInt(1, item.getItemid());
				ResultSet rs2 = ps.executeQuery();
				double sum = 0.0;
				double qty = 0.0;
				while (rs2.next()) {
					qty = qty + rs2.getDouble("qty");
					sum = (rs2.getDouble("unitcost") * qty);
				}
				if (qty == 0.0) {
					item.setUnitcost(0.0);
				} else {
					BigDecimal a = new BigDecimal(sum / qty);
					double result = a.setScale(2, BigDecimal.ROUND_HALF_UP)
							.doubleValue();
					item.setUnitcost(result);
				}
				BigDecimal temp = new BigDecimal(qty);
				item.setQty(temp.setScale(2, BigDecimal.ROUND_HALF_UP)
						.doubleValue());
				temp = new BigDecimal(sum);
				item.setPrice(temp.setScale(2, BigDecimal.ROUND_HALF_UP)
						.doubleValue());

				rs2.close();
				ps.close();

				item.setName(rs.getString("name"));
				item.setOrgid(user.getOrgid());
				item.setUnit(rs.getString("unit"));
				item.setCatid(rs.getInt("catid"));

				PreparedStatement past3 = conn.prepareStatement(sql3);
				past3.setInt(1, item.getCatid());
				past3.setInt(2, user.getOrgid());
				ResultSet rs3 = past3.executeQuery();
				if (rs3.next()) {
					item.setSecond(rs3.getString(1));
					int id = rs3.getInt(2);
					String sql4 = "select name from category where catid = ? and orgid = ?";
					PreparedStatement past4 = conn.prepareStatement(sql4);
					past4.setInt(1, id);
					past4.setInt(2, user.getOrgid());
					ResultSet rs4 = past4.executeQuery();
					if (rs4.next()) {
						item.setFirst(rs4.getString(1));
						rs4.close();
						past4.close();
					}
					rs3.close();
					past3.close();
				}
				list.add(item);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public boolean additem(ItemBean item, UserBean user, int catid) {
		boolean flag = false;

		String sql1 = "insert into item(name,description,orgid,unit,catid) values("
				+ "'"
				+ item.getName()
				+ "'"
				+ ","
				+ "'"
				+ item.getDescription()
				+ "'"
				+ ","
				+ "'"
				+ user.getOrgid()
				+ "'"
				+ ","
				+ "'"
				+ item.getUnit()
				+ "'"
				+ ","
				+ "'"
				+ catid
				+ "'" + ")";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql1);
			past.executeUpdate();
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public ItemBean showoneitem(int itemid) {
		ItemBean item = new ItemBean();
		boolean flag = false;
		int parentid = 0;

		String sql1 = "select * from item where itemid = ?";
		String sql2 = "select name,parentid from category where catid = ?";

		Connection conn = db.getConnection();
		PreparedStatement ps1 = null;
		ResultSet rs1 = null;

		try {
			ps1 = conn.prepareStatement(sql1);
			ps1.setInt(1, itemid);
			rs1 = ps1.executeQuery();
			if (rs1.next()) {
				item.setCatid(rs1.getInt("catid"));
				item.setDescription(rs1.getString("description"));
				item.setItemid(itemid);
				item.setName(rs1.getString("name"));
				item.setUnit(rs1.getString("unit"));
				flag = true;
			}
			rs1.close();
			ps1.close();
			if (flag) {
				ps1 = conn.prepareStatement(sql2);
				ps1.setInt(1, item.getCatid());
				rs1 = ps1.executeQuery();
				if (rs1.next()) {
					item.setSecond(rs1.getString(1));
					parentid = rs1.getInt(2);
				}
				rs1.close();
				ps1.close();
				if (parentid != 0) {
					ps1 = conn.prepareStatement(sql2);
					ps1.setInt(1, parentid);
					rs1 = ps1.executeQuery();
					if (rs1.next()) {
						item.setFirst(rs1.getString(1));
					}
					rs1.close();
					ps1.close();
				} else {
					item.setFirst(item.getSecond());
				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return item;
	}

	public boolean modifyitem(ItemBean item) {

		boolean flag = false;
		String sql1 = null;
		
		if(item.getCatid() != 0){
			sql1 = " update item set name=?, catid=?, description=?, unit=? where itemid = ?";
		}else{
			sql1 = " update item set name=?, description=?, unit=? where itemid = ?";
		}
		

		Connection conn = db.getConnection();
		PreparedStatement ps1 = null;

		try {
			ps1 = conn.prepareStatement(sql1);
			
			if(item.getCatid() != 0){
				ps1.setString(1, item.getName());
				ps1.setInt(2, item.getCatid());
				ps1.setString(3, item.getDescription());
				ps1.setString(4, item.getUnit());
				ps1.setInt(5, item.getItemid());
			}else{
				ps1.setString(1, item.getName());
				ps1.setString(2, item.getDescription());
				ps1.setString(3, item.getUnit());
				ps1.setInt(4, item.getItemid());
			}
			
			ps1.executeUpdate();
			ps1.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List showfirst(UserBean user) {
		List<CategoryBean> list = new ArrayList<CategoryBean>();
		String sql1 = "select * from category where orgid=? and parentid=0";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			rs = past.executeQuery();
			while (rs.next()) {
				CategoryBean cat = new CategoryBean();
				cat.setCatid(rs.getInt("catid"));
				cat.setFirst(rs.getString("name"));
				cat.setParentid(0);
				list.add(cat);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List showsecond(UserBean user, int catid) {
		List<CategoryBean> list = new ArrayList<CategoryBean>();
		String sql1 = "select * from category where orgid=? and parentid = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			past.setInt(2, catid);
			rs = past.executeQuery();
			while (rs.next()) {
				CategoryBean cat = new CategoryBean();
				cat.setCatid(rs.getInt("catid"));
				cat.setSecond(rs.getString("name"));
				cat.setParentid(0);
				list.add(cat);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}
}
